package com.cs157a.dao;


import java.sql.*;
import java.util.*;
import java.math.*;


 /**
  * AplCar Data Access Object (DAO).
  * !!NOTE: need Connection variable by DBCounnection.getDBConnection()
  * This class contains all database handling that is needed to 
  * permanently store and retrieve AplCar object instances. 
  */



public class AplCarDao {

	private String table = "APLOLD";
	
	//DBConnection need to be created on controller or view side
	//DBConnection dbconn = new DBConnection();

	public void setTable(String tableName) { //set current working table name
		table=tableName;
	}
	
	public String getTable(){ //get current working table name
		return table;
	}


    /**
     * createValueObject-method. This method is used when the Dao class needs
     * to create new value object instance. The reason why this method exists
     * is that sometimes the programmer may want to extend also the valueObject
     * and then this method can be overrided to return extended valueObject.
     * NOTE: If you extend the valueObject class, make sure to override the
     * clone() method in it!
     */
    public AplCar createValueObject() {
    	return new AplCar();
    }


    /**
     * getObject-method. This will create and load valueObject contents from database 
     * using given Primary-Key as identifier. This method is just a convenience method 
     * for the real load-method which accepts the valueObject as a parameter. Returned
     * valueObject will be created using the createValueObject() method.
     */
    public AplCar getObject(Connection conn, String Model, String Year,
    						String EngineType) throws NotFoundException, SQLException {
//    		String Description, String CubicInches, String Litres,

          AplCar valueObject = createValueObject();
          valueObject.setModel(Model);
          valueObject.setYear(Year);
//          valueObject.setDescription(Description);
//          valueObject.setLitres(Litres);
          valueObject.setEngineType(EngineType);
//          valueObject.setCubicInches(CubicInches);
          load(conn, valueObject);
          return valueObject;
    }


    /**
     * load-method. This will load valueObject contents from database using
     * Primary-Key as identifier. Upper layer should use this so that valueObject
     * instance is created and only primary-key should be specified. Then call
     * this method to complete other persistent information. This method will
     * overwrite all other fields except primary-key and possible runtime variables.
     * If load can not find matching row, NotFoundException will be thrown.
     *
     * @param conn         This method requires working database connection.
     * @param valueObject  This parameter contains the class instance to be loaded.
     *                     Primary-key field must be set for this to work properly.
     */
    public void load(Connection conn, AplCar valueObject) throws NotFoundException, SQLException {

          if (valueObject.getModel() == null) {
               //System.out.println("Can not select without Primary-Key!");
               throw new NotFoundException("Can not select without Primary-Key!");
          }

          if (valueObject.getYear() == null) {
               //System.out.println("Can not select without Primary-Key!");
               throw new NotFoundException("Can not select without Primary-Key!");
          }

//          if (valueObject.getDescription() == null) {
//               //System.out.println("Can not select without Primary-Key!");
//               throw new NotFoundException("Can not select without Primary-Key!");
//          }
//
//          if (valueObject.getLitres() == null) {
//               //System.out.println("Can not select without Primary-Key!");
//               throw new NotFoundException("Can not select without Primary-Key!");
//          }

          if (valueObject.getEngineType() == null) {
               //System.out.println("Can not select without Primary-Key!");
               throw new NotFoundException("Can not select without Primary-Key!");
          }

//          if (valueObject.getCubicInches() == null) {
//               //System.out.println("Can not select without Primary-Key!");
//               throw new NotFoundException("Can not select without Primary-Key!");
//          }

          String sql = "SELECT * FROM "+table+" WHERE (MODEL = ? AND YEAR = ? AND " +
//          		"DESCRIPTION = ? AND "
//               + "LITRES = ? AND " +
               "ENGINE_TYPE = ? " +
//               "AND CUBIC_INCHES = ? " +
               ") "; 
          PreparedStatement stmt = null;

          try {
               stmt = conn.prepareStatement(sql);
               stmt.setString(1, valueObject.getModel()); 
               stmt.setString(2, valueObject.getYear()); 
//               stmt.setString(3, valueObject.getDescription()); 
//               stmt.setString(4, valueObject.getLitres()); 
               stmt.setString(3, valueObject.getEngineType()); 
//               stmt.setString(6, valueObject.getCubicInches()); 

               singleQuery(conn, stmt, valueObject);

          } finally {
              if (stmt != null)
                  stmt.close();
          }
    }


    /**
     * LoadAll-method. This will read all contents from database table and
     * build a List containing valueObjects. Please note, that this method
     * will consume huge amounts of resources if table has lot's of rows. 
     * This should only be used when target tables have only small amounts
     * of data.
     *
     * @param conn         This method requires working database connection.
     */
    public List loadAll(Connection conn) throws SQLException {

          String sql = "SELECT * FROM "+table+" ORDER BY CUBIC_INCHES ASC ";
          List searchResults = listQuery(conn, conn.prepareStatement(sql));

          return searchResults;
    }



    /**
     * create-method. This will create new row in database according to supplied
     * valueObject contents. Make sure that values for all NOT NULL columns are
     * correctly specified. Also, if this table does not use automatic surrogate-keys
     * the primary-key must be specified. After INSERT command this method will 
     * read the generated primary-key back to valueObject if automatic surrogate-keys
     * were used. 
     *
     * @param conn         This method requires working database connection.
     * @param valueObject  This parameter contains the class instance to be created.
     *                     If automatic surrogate-keys are not used the Primary-key 
     *                     field must be set for this to work properly.
     */
    public synchronized void create(Connection conn, AplCar valueObject) throws SQLException {

          String sql = "";
          PreparedStatement stmt = null;
          ResultSet result = null;

          try {
               sql = "INSERT INTO "+table+" ( MODEL, YEAR, DESCRIPTION, "
               + "LITRES, ENGINE_TYPE, CUBIC_INCHES, "
               + "RLINK) VALUES (?, ?, ?, ?, ?, ?, ?) ";
               stmt = conn.prepareStatement(sql);

               stmt.setString(1, valueObject.getModel()); 
               stmt.setString(2, valueObject.getYear()); 
               stmt.setString(3, valueObject.getDescription()); 
               stmt.setString(4, valueObject.getLitres()); 
               stmt.setString(5, valueObject.getEngineType()); 
               stmt.setString(6, valueObject.getCubicInches()); 
               stmt.setInt(7, valueObject.getRlink()); 

               int rowcount = databaseUpdate(conn, stmt);
               if (rowcount != 1) {
                    //System.out.println("PrimaryKey Error when updating DB!");
                    throw new SQLException("PrimaryKey Error when updating DB!");
               }

          } finally {
              if (stmt != null)
                  stmt.close();
          }


    }


    /**
     * save-method. This method will save the current state of valueObject to database.
     * Save can not be used to create new instances in database, so upper layer must
     * make sure that the primary-key is correctly specified. Primary-key will indicate
     * which instance is going to be updated in database. If save can not find matching 
     * row, NotFoundException will be thrown.
     *
     * @param conn         This method requires working database connection.
     * @param valueObject  This parameter contains the class instance to be saved.
     *                     Primary-key field must be set for this to work properly.
     */
    public void save(Connection conn, AplCar valueObject) 
          throws NotFoundException, SQLException {

          String sql = "UPDATE "+table+" SET RLINK = ? WHERE (MODEL = ? AND YEAR = ? AND DESCRIPTION = ? AND "
               + "LITRES = ? AND ENGINE_TYPE = ? AND CUBIC_INCHES = ? ) ";
          PreparedStatement stmt = null;

          try {
              stmt = conn.prepareStatement(sql);
              stmt.setInt(1, valueObject.getRlink()); 

              stmt.setString(2, valueObject.getModel()); 
              stmt.setString(3, valueObject.getYear()); 
              stmt.setString(4, valueObject.getDescription()); 
              stmt.setString(5, valueObject.getLitres()); 
              stmt.setString(6, valueObject.getEngineType()); 
              stmt.setString(7, valueObject.getCubicInches()); 

              int rowcount = databaseUpdate(conn, stmt);
              if (rowcount == 0) {
                   //System.out.println("Object could not be saved! (PrimaryKey not found)");
                   throw new NotFoundException("Object could not be saved! (PrimaryKey not found)");
              }
              if (rowcount > 1) {
                   //System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
                   throw new SQLException("PrimaryKey Error when updating DB! (Many objects were affected!)");
              }
          } finally {
              if (stmt != null)
                  stmt.close();
          }
    }


    /**
     * delete-method. This method will remove the information from database as identified by
     * by primary-key in supplied valueObject. Once valueObject has been deleted it can not 
     * be restored by calling save. Restoring can only be done using create method but if 
     * database is using automatic surrogate-keys, the resulting object will have different 
     * primary-key than what it was in the deleted object. If delete can not find matching row,
     * NotFoundException will be thrown.
     *
     * @param conn         This method requires working database connection.
     * @param valueObject  This parameter contains the class instance to be deleted.
     *                     Primary-key field must be set for this to work properly.
     */
    public void delete(Connection conn, AplCar valueObject) 
          throws NotFoundException, SQLException {

          if (valueObject.getModel() == null) {
               //System.out.println("Can not delete without Primary-Key!");
               throw new NotFoundException("Can not delete without Primary-Key!");
          }

          if (valueObject.getYear() == null) {
               //System.out.println("Can not delete without Primary-Key!");
               throw new NotFoundException("Can not delete without Primary-Key!");
          }

          if (valueObject.getDescription() == null) {
               //System.out.println("Can not delete without Primary-Key!");
               throw new NotFoundException("Can not delete without Primary-Key!");
          }

          if (valueObject.getLitres() == null) {
               //System.out.println("Can not delete without Primary-Key!");
               throw new NotFoundException("Can not delete without Primary-Key!");
          }

          if (valueObject.getEngineType() == null) {
               //System.out.println("Can not delete without Primary-Key!");
               throw new NotFoundException("Can not delete without Primary-Key!");
          }

          if (valueObject.getCubicInches() == null) {
               //System.out.println("Can not delete without Primary-Key!");
               throw new NotFoundException("Can not delete without Primary-Key!");
          }

          String sql = "DELETE FROM "+table+" WHERE (MODEL = ? AND YEAR = ? AND DESCRIPTION = ? AND "
               + "LITRES = ? AND ENGINE_TYPE = ? AND CUBIC_INCHES = ? ) ";
          PreparedStatement stmt = null;

          try {
              stmt = conn.prepareStatement(sql);
              stmt.setString(1, valueObject.getModel()); 
              stmt.setString(2, valueObject.getYear()); 
              stmt.setString(3, valueObject.getDescription()); 
              stmt.setString(4, valueObject.getLitres()); 
              stmt.setString(5, valueObject.getEngineType()); 
              stmt.setString(6, valueObject.getCubicInches()); 

              int rowcount = databaseUpdate(conn, stmt);
              if (rowcount == 0) {
                   //System.out.println("Object could not be deleted (PrimaryKey not found)");
                   throw new NotFoundException("Object could not be deleted! (PrimaryKey not found)");
              }
              if (rowcount > 1) {
                   //System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
                   throw new SQLException("PrimaryKey Error when updating DB! (Many objects were deleted!)");
              }
          } finally {
              if (stmt != null)
                  stmt.close();
          }
    }


    /**
     * deleteAll-method. This method will remove all information from the table that matches
     * this Dao and ValueObject couple. This should be the most efficient way to clear table.
     * Once deleteAll has been called, no valueObject that has been created before can be 
     * restored by calling save. Restoring can only be done using create method but if database 
     * is using automatic surrogate-keys, the resulting object will have different primary-key 
     * than what it was in the deleted object. (Note, the implementation of this method should
     * be different with different DB backends.)
     *
     * @param conn         This method requires working database connection.
     */
    public void deleteAll(Connection conn) throws SQLException {

          String sql = "DELETE FROM "+table+"";
          PreparedStatement stmt = null;

          try {
              stmt = conn.prepareStatement(sql);
              int rowcount = databaseUpdate(conn, stmt);
          } finally {
              if (stmt != null)
                  stmt.close();
          }
    }


    /**
     * coutAll-method. This method will return the number of all rows from table that matches
     * this Dao. The implementation will simply execute "select count(primarykey) from table".
     * If table is empty, the return value is 0. This method should be used before calling
     * loadAll, to make sure table has not too many rows.
     *
     * @param conn         This method requires working database connection.
     */
    public int countAll(Connection conn) throws SQLException {

          String sql = "SELECT count(*) FROM "+table+"";
          PreparedStatement stmt = null;
          ResultSet result = null;
          int allRows = 0;

          try {
              stmt = conn.prepareStatement(sql);
              result = stmt.executeQuery();

              if (result.next())
                  allRows = result.getInt(1);
          } finally {
              if (result != null)
                  result.close();
              if (stmt != null)
                  stmt.close();
          }
          return allRows;
    }


    /** 
     * searchMatching-Method. This method provides searching capability to 
     * get matching valueObjects from database. It works by searching all 
     * objects that match permanent instance variables of given object.
     * Upper layer should use this by setting some parameters in valueObject
     * and then  call searchMatching. The result will be 0-N objects in a List, 
     * all matching those criteria you specified. Those instance-variables that
     * have NULL values are excluded in search-criteria.
     *
     * @param conn         This method requires working database connection.
     * @param valueObject  This parameter contains the class instance where search will be based.
     *                     Primary-key field should not be set.
     */
    public List searchMatching(Connection conn, AplCar valueObject) throws SQLException {

          List searchResults;

          boolean first = true;
          StringBuffer sql = new StringBuffer("SELECT * FROM "+table+" WHERE 1=1 ");

          if (valueObject.getModel() != null) {
              if (first) { first = false; }
              sql.append("AND MODEL LIKE '").append(valueObject.getModel()).append("%' ");
          }

          if (valueObject.getYear() != null) {
              if (first) { first = false; }
              sql.append("AND YEAR LIKE '").append(valueObject.getYear()).append("%' ");
          }

          if (valueObject.getDescription() != null) {
              if (first) { first = false; }
              sql.append("AND DESCRIPTION LIKE '").append(valueObject.getDescription()).append("%' ");
          }

          if (valueObject.getLitres() != null) {
              if (first) { first = false; }
              sql.append("AND LITRES LIKE '").append(valueObject.getLitres()).append("%' ");
          }

          if (valueObject.getEngineType() != null) {
              if (first) { first = false; }
              sql.append("AND ENGINE_TYPE LIKE '").append(valueObject.getEngineType()).append("%' ");
          }

          if (valueObject.getCubicInches() != null) {
              if (first) { first = false; }
              sql.append("AND CUBIC_INCHES LIKE '").append(valueObject.getCubicInches()).append("%' ");
          }

          if (valueObject.getRlink() != 0) {
              if (first) { first = false; }
              sql.append("AND RLINK = ").append(valueObject.getRlink()).append(" ");
          }


          sql.append("ORDER BY CUBIC_INCHES ASC ");

          // Prevent accidential full table results.
          // Use loadAll if all rows must be returned.
          if (first)
               searchResults = new ArrayList();
          else
               searchResults = listQuery(conn, conn.prepareStatement(sql.toString()));

          return searchResults;
    }




    /**
     * databaseUpdate-method. This method is a helper method for internal use. It will execute
     * all database handling that will change the information in tables. SELECT queries will
     * not be executed here however. The return value indicates how many rows were affected.
     * This method will also make sure that if cache is used, it will reset when data changes.
     *Transaction Added
     * @param conn         This method requires working database connection.
     * @param stmt         This parameter contains the SQL statement to be excuted.
     */
    protected int databaseUpdate(Connection conn, PreparedStatement stmt) throws SQLException {

          int result = -1;
    	  conn.setAutoCommit(false);// begin transaction 
          Savepoint savepoint = conn.setSavepoint("carSavePoint");
          try {
        	  result = stmt.executeUpdate();
        	  conn.commit();//end transaction 
          }
          catch (Exception exc) {
        	  conn.rollback(savepoint);// cancel transaction, rollback to savepoint
        	  exc.printStackTrace();
          }
    	  conn.setAutoCommit(true);// exit transaction 
          return result;
    }



    /**
     * databaseQuery-method. This method is a helper method for internal use. It will execute
     * all database queries that will return only one row. The resultset will be converted
     * to valueObject. If no rows were found, NotFoundException will be thrown.
     *
     * @param conn         This method requires working database connection.
     * @param stmt         This parameter contains the SQL statement to be excuted.
     * @param valueObject  Class-instance where resulting data will be stored.
     */
    protected void singleQuery(Connection conn, PreparedStatement stmt, AplCar valueObject) 
          throws NotFoundException, SQLException {

          ResultSet result = null;

          try {
              result = stmt.executeQuery();

              if (result.next()) {

            	   valueObject.Make = table;
                   valueObject.setModel(result.getString("MODEL")); 
                   valueObject.setYear(result.getString("YEAR")); 
                   valueObject.setDescription(result.getString("DESCRIPTION")); 
                   valueObject.setLitres(result.getString("LITRES")); 
                   valueObject.setEngineType(result.getString("ENGINE_TYPE")); 
                   valueObject.setCubicInches(result.getString("CUBIC_INCHES")); 
                   valueObject.setRlink(result.getInt("RLINK")); 

              } else {
                    //System.out.println(""+table+" Object Not Found!");
                    throw new NotFoundException(""+table+" Object Not Found!");
              }
          } finally {
              if (result != null)
                  result.close();
              if (stmt != null)
                  stmt.close();
          }
    }


    /**
     * databaseQuery-method. This method is a helper method for internal use. It will execute
     * all database queries that will return multiple rows. The resultset will be converted
     * to the List of valueObjects. If no rows were found, an empty List will be returned.
     *
     * @param conn         This method requires working database connection.
     * @param stmt         This parameter contains the SQL statement to be excuted.
     */
    protected List listQuery(Connection conn, PreparedStatement stmt) throws SQLException {

          ArrayList searchResults = new ArrayList();
          ResultSet result = null;

          try {
              result = stmt.executeQuery();

              while (result.next()) {
                   AplCar temp = createValueObject();

                   temp.Make = table;
                   temp.setModel(result.getString("MODEL")); 
                   temp.setYear(result.getString("YEAR")); 
                   temp.setDescription(result.getString("DESCRIPTION")); 
                   temp.setLitres(result.getString("LITRES")); 
                   temp.setEngineType(result.getString("ENGINE_TYPE")); 
                   temp.setCubicInches(result.getString("CUBIC_INCHES")); 
                   temp.setRlink(result.getInt("RLINK")); 

                   searchResults.add(temp);
              }

          } finally {
              if (result != null)
                  result.close();
              if (stmt != null)
                  stmt.close();
          }

          return (List)searchResults;
    }

	/**
	 * main routine for testing purpose
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		DBConnection dbconn = new DBConnection();
		
		AplCarDao cardao = new AplCarDao();
		AplCar acar = new AplCar();
		System.out.println("current table="+cardao.getTable());
		try {
			acar = cardao.getObject(dbconn.getDBConnection(), "STARFIRE", "65", "V8");
		} catch (NotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println(acar.toString());
		
		dbconn.disconnectFromDB();

	}

    public void create(Connection connection, AplCar autoModel, String maker) throws SQLException {
    	maker = MakeDao.getMakeCode(connection, maker);
        this.setTable("APL" + maker);
        createTable(connection, maker);
        create(connection, autoModel);
    }
     public void createTable(Connection conn, String maker) throws SQLException {
        if(!isTableExisted(conn, maker))
        {
            createMakerTable(conn, maker);
        }
        
    }

    private void createMakerTable(Connection conn, String maker) throws SQLException {
        String sql = "Create Table APL" + maker + " (MODEL VARCHAR2(25)    NOT NULL,"
                + " YEAR       VARCHAR2(4) NOT NULL,"
                + " DESCRIPTION    VARCHAR2(51),"
                + " LITRES     VARCHAR2(4),"
                + " ENGINE_TYPE    VARCHAR2(3),"
                + " CUBIC_INCHES   VARCHAR2(6),"
                + " RLINK      NUMBER(4),"
                + " PRIMARY KEY    (MODEL, YEAR, DESCRIPTION, LITRES, ENGINE_TYPE, CUBIC_INCHES, RLINK),"
                + " FOREIGN KEY    (RLINK) REFERENCES RADCRX)";

        Statement st = conn.createStatement();
        st.executeQuery(sql);
        st.close();
    }
    private boolean isTableExisted(Connection conn, String maker)
    {
        String sql = "select * from APL" + maker;
        Statement st;
        try {
            st = conn.createStatement();
            st.executeQuery(sql);
            st.close();

        } catch (SQLException ex) {
            return false;
        }
        return true;
    }

}


             